Imports PTT.CarRes.Data

Public Class ClassQueryInvWarea
    Public Function Add_Delete(ByVal dt As DataSet, ByVal Strconn As String) As Boolean
        Dim ClsInvWarea As New ClassInvWarea
        Dim sql As String = ""
        Dim i As Integer
        Dim sqlComd As OleDb.OleDbCommand
        Dim Conn As OleDb.OleDbConnection
        Dim vTran As OleDb.OleDbTransaction
        Conn = ClsInvWarea.ConnectionOpen(Strconn)
        vTran = ClsInvWarea.BeginTran(Conn)
        Dim ret As Boolean = False
        sql = "Delete * from InvWarea"
        sqlComd = New OleDb.OleDbCommand("Delete  from InvWarea", Conn, vTran)
        Try
            sqlComd.ExecuteNonQuery()

            For i = 0 To dt.Tables(0).Rows.Count - 1
                With ClsInvWarea
                    .WareaId = dt.Tables(0).Rows(i).Item(0)
                    .WareaGroupId = dt.Tables(0).Rows(i).Item(1)
                    If .USP_Save(Conn, vTran) = True Then
                        ret = True
                    Else
                        ret = False
                        GoTo ret
                    End If
                End With
            Next
        Catch ex As Exception
            ret = False
        End Try
ret:    If ret = False Then
            ClsInvWarea.RollBackTran(vTran)
        Else
            ClsInvWarea.CommitTran(vTran)
        End If
        ClsInvWarea.ConnectionClose(Conn)
        Return ret
    End Function
    Public Function Search_Employee(ByVal EmpCode As String, ByVal EmpId As String, ByVal WareaGroup As String, ByVal Strconn As String, ByVal st As String) As DataSet
        Dim cLsClass As New Class_Connection
        Dim Dset As New DataSet
        Dim sql As String = ""
        Dim i As Integer
        Dim strsql As String
        Dim ret As Boolean = False
        Dim Query As PTT.CarRes.Data.QueryInvWareas
        'Query = Search(EmpCode, "", Strconn)
        'If Not Query Is Nothing Then
            '/////////
            Dim dT As New DataTable
            Dim nEwRow As DataRow
            Dim ii As Integer
            Dim xCount As Integer
            dT = New DataTable("TableA")
            dT.Columns.Add("Code", GetType(String))
            dT.Columns.Add("EmpName", GetType(String))
        '/////////
        Query = Search("", WareaGroup, Strconn)
            If Not Query Is Nothing Then
                Dim count As Integer = Query.Count - 1
                Dim Sum(count) As String
                For i = 0 To count
                    Sum(i) = Query(i).WareaId
                Next
                Dim Conn As New OleDb.OleDbConnection
                Conn = cLsClass.ConnectionOpen(st)
                ' For i = 0 To Count 
                strsql = " SELECT   personel_info.CODE, personel_info.INAME + '' + personel_info.FNAME + '  ' + personel_info.LNAME AS EmpName "
                ' strsql = strsql & " ,[position].posname, personel_info.JOBGROUP,personel_info.UNITCODE, unit.unitabbr,personel_info.FNAME "
                strsql = strsql & " FROM personel_info INNER JOIN"
                strsql = strsql & " [position] ON personel_info.POSCODE = [position].poscode INNER JOIN"
                strsql = strsql & " unit ON personel_info.UNITCODE = unit.unitcode "
                strsql = strsql & " Where (personel_info.WSTCODE IN ('A','B','I','J')) "
            'strsql = strsql & " AND (personel_info.OFFICECODE in("
            'For i = 0 To count
            '    If i = count Then
            '        strsql = strsql & " '" & Sum(i) & "'))"
            '    Else
            '        strsql = strsql & "'" & Sum(i) & "' ,"
            '    End If
            'Next
                If EmpId <> "" Then
                    strsql = strsql & " AND (personel_info.CODE = '" & EmpId & "' "
                    strsql = strsql & " OR personel_info.FNAME Like '%" & EmpId & "%' "
                    strsql = strsql & " OR personel_info.LNAME Like '%" & EmpId & "%'"
                    strsql = strsql & " OR personel_info.INAME + '' + personel_info.FNAME Like '%" & EmpId & "%')"
                End If
                strsql = strsql & " Order by personel_info.Code"

                Dset = cLsClass.BindData(Conn, , , strsql)
                If Not Dset Is Nothing Then
                    xCount = Dset.Tables(0).Rows.Count - 1
                    For ii = 0 To xCount
                        nEwRow = dT.NewRow
                        nEwRow.Item("Code") = Dset.Tables(0).Rows(ii).Item(0)
                        nEwRow.Item("EmpName") = Dset.Tables(0).Rows(ii).Item(1)
                        dT.Rows.Add(nEwRow)
                    Next
                End If
                'Next
                cLsClass.ConnectionClose(Conn)
                Dset.Tables.Add(dT)
            Else
                Dset = Nothing
            End If
        'Else
        '    Dset = Nothing
        'End If
        Return Dset
    End Function

    Public Function Add(ByVal InvWarea As QueryInvWarea, ByVal Strconn As String) As Boolean
        Dim ClsInvWarea As New ClassInvWarea
        Dim sql As String = ""
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvWarea.ConnectionOpen(Strconn)
        Dim ret As Boolean = False
        With ClsInvWarea
            .WareaId = InvWarea.WareaId
            .WareaGroupId = InvWarea.WareaGroupId
            ret = .USP_Save(Conn)
        End With
        ClsInvWarea.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Edit(ByVal InvWarea As QueryInvWarea, ByVal Strconn As String) As Boolean
        Dim ClsInvWarea As New ClassInvWarea
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvWarea.ConnectionOpen(Strconn)
        Dim ret As Boolean = False
        With ClsInvWarea
            .WareaId = InvWarea.WareaId
            .Load(Conn)
            .WareaGroupId = InvWarea.WareaGroupId
            ret = .USP_Update(Conn)
        End With
        ClsInvWarea.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Delete(ByVal InvWarea As QueryInvWarea, ByVal Strconn As String) As Boolean
        Dim ClsInvWarea As New ClassInvWarea
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvWarea.ConnectionOpen(Strconn)
        Dim ret As Boolean = False
        With ClsInvWarea
            .WareaId = InvWarea.WareaId
            .Load(Conn)
            ret = .USP_Delete(Conn)
        End With
        ClsInvWarea.ConnectionClose(Conn)
        Return ret
    End Function

    Public Function Search(Optional ByVal ParaWareaId As String = "", _
    Optional ByVal ParaWareaGroupId As String = "", Optional ByVal StrConn As String = "") As QueryInvWareas
        Dim ClsInvWarea As New ClassInvWarea
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvWarea.ConnectionOpen(StrConn)
        Dim ret As QueryInvWareas = Nothing
        Dim str As String = ""
        Dim Condition As String = ""
        str &= " Select * from InvWarea"
        If ParaWareaId & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "(WareaId = '" & ParaWareaId & "')"
        End If
        If ParaWareaGroupId & "" <> "" Then
            If Condition & "" <> "" Then
                Condition &= " and "
            End If
            Condition &= "(WareaGroupId = '" & ParaWareaGroupId & "')"
        End If

        If Condition & "" <> "" Then
            str &= " where "
            str &= Condition
        End If
        Dim ds As DataSet
        With ClsInvWarea
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvWareas

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)

                            results.Add(New QueryInvWarea( _
                              .Item("WareaId") & "", _
                              .Item("WareaGroupId") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvWarea.ConnectionClose(Conn)
        Return ret
    End Function
    Public Function Load(Optional ByVal ParaWareaId As String = "", Optional ByVal StrConn As String = "") As QueryInvWareas
        Dim ClsInvWarea As New ClassInvWarea
        Dim Conn As OleDb.OleDbConnection
        Conn = ClsInvWarea.ConnectionOpen(StrConn)
        Dim ret As QueryInvWareas = Nothing
        Dim str As String = ""
        Dim Condition As String = ""
        str &= " Select * from InvWarea"
        If ParaWareaId & "" <> "" Then
            If Condition & "" <> "" Then Condition &= " and "
            Condition &= "(WareaId = " & ParaWareaId & ")"
        End If

        If Condition & "" <> "" Then
            str &= " where "
            str &= Condition
        Else
            Return Nothing
            Exit Function
        End If
        Dim ds As DataSet
        With ClsInvWarea
            ds = .BindData(Conn, , , str)
            If Not ds Is Nothing Then
                Dim i As Integer
                Dim results As New QueryInvWareas

                Try
                    For i = 0 To ds.Tables(0).Rows.Count - 1
                        With ds.Tables(0).Rows(i)

                            results.Add(New QueryInvWarea( _
                              .Item("WareaId") & "", _
                              .Item("WareaGroupId") & "" _
                            ))
                        End With
                    Next
                    ret = results
                Catch ex As Exception
                    Return Nothing
                    'Throw New DALException(ex.Message)
                End Try
            End If
        End With
        ClsInvWarea.ConnectionClose(Conn)
        Return ret
    End Function
End Class
